from flask import Flask, render_template, request, redirect, url_for, jsonify, session
import csv
import os
from attendance_db import init_db, insert_attendance, get_all_attendance, update_attendance, delete_attendance_by_date_and_type
from functools import wraps
from flask import send_file
import requests
import pandas as pd  # 新增 pandas 支持 excel 读取
from difflib import get_close_matches

app = Flask(__name__)
app.secret_key = os.environ.get('FLASK_SECRET_KEY', 'a_default_secret_key_for_development_only')

# 初始化数据库
init_db()

def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not session.get('logged_in'):
            return redirect(url_for('login', next=request.url))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/login', methods=['GET', 'POST'])
def login():
    error = None
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        # 简单硬编码，实际可用数据库或env
        if username == os.environ.get('FLASK_USERNAME', 'admin') and password == os.environ.get('FLASK_PASSWORD', 'password'):
            session['logged_in'] = True
            return redirect(url_for('attendance_table'))
        else:
            error = '用户名或密码错误'
    return render_template('login.html', error=error)

@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    return redirect(url_for('attendance_table'))

@app.route('/')
@login_required
def index():
    # 首页只允许登录用户访问考勤上传页面
    return render_template('attendance_upload.html', logged_in=True)


def get_join_dates():
    """获取所有成员的入帮日期。
    从`帮众名单`类型的记录中，读取`remark`字段作为入帮日期。
    如果一个成员有多条记录，取最早的日期为入帮日期。
    """
    join_dates = {}
    all_data = get_all_attendance()
    member_list_records = [r for r in all_data if r[3] == '帮众名单' and r[5]]

    from datetime import datetime
    def parse_join_date(d_str):
        # 支持多种日期格式
        for fmt in ('%Y-%m-%d', '%m-%d-%Y', '%Y/%m/%d', '%m/%d/%Y'):
            try:
                return datetime.strptime(d_str.strip(), fmt)
            except (ValueError, TypeError):
                continue
        return None

    for player_name, _, _, _, _, remark in member_list_records:
        join_date = parse_join_date(remark)
        if join_date:
            # 如果已有日期，取更早的
            if player_name not in join_dates or join_date < join_dates[player_name]:
                join_dates[player_name] = join_date
    return join_dates


from attendance_db import get_latest_bangzhong_list
def get_latest_member_dict():
    """获取最新帮众名单（type=帮众名单），返回dict: name->job"""
    member_dict = {}
    for player_name, job in get_latest_bangzhong_list():
        member_dict[player_name] = job
    return member_dict

@app.route('/export_attendance')
@login_required
def export_attendance():
    import io
    import csv
    selected_date = request.args.get('date')
    selected_type = request.args.get('type')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    data = get_all_attendance()
    join_dates = get_join_dates()  # 获取入帮日期
    from attendance_db import get_latest_bangzhong_list
    players = get_latest_bangzhong_list()  # [(player_name, job), ...]
    date_types = set()
    from datetime import datetime
    def parse_db_date(d):
        try:
            return datetime.strptime(d, '%m-%d-%Y')
        except Exception:
            return None
    def parse_input_date(d):
        try:
            return datetime.strptime(d, '%Y-%m-%d')
        except Exception:
            return None
    start_dt = parse_input_date(start_date) if start_date else None
    end_dt = parse_input_date(end_date) if end_date else None
    filtered_records = []
    for player_name, job, date, type_, status, remark in data:
        if selected_date and date != selected_date:
            continue
        if selected_type and type_ != selected_type:
            continue
        if type_ == '帮众名单':
            continue
        date_dt = parse_db_date(date)
        if start_dt and (not date_dt or date_dt < start_dt):
            continue
        if end_dt and (not date_dt or date_dt > end_dt):
            continue
        filtered_records.append((player_name, date, type_, status))
        date_types.add((date, type_))
    # 优化列顺序：先按日期升序，同一天内接龙优先，其余按类型
    def type_priority(t):
        if t == '接龙':
            return 0
        elif t.startswith('帮战'):
            return 1
        else:
            return 2
    date_types = sorted(list(date_types), key=lambda x: (parse_db_date(x[0]), type_priority(x[1]), x[1]))
    # 构造表格：玩家为行，日期/类型为列，单元格为状态（只用成员名称做主键，聚合所有职业）
    table = {}
    for player_name, date, type_, status in filtered_records:
        table.setdefault(player_name, {})[(date, type_)] = status
    output = io.StringIO()
    writer = csv.writer(output)
    # 表头
    header = ['玩家名称', '职业'] + [f'{date} {type_}' for date, type_ in date_types]
    writer.writerow(header)
    # 行
    for player_name, job in players:
        row = [player_name, job]
        join_date = join_dates.get(player_name)
        for date, type_ in date_types:
            val = table.get(player_name, {}).get((date, type_), '缺勤')
            event_date = parse_db_date(date)
            if val == '缺勤' and join_date and event_date and event_date < join_date:
                val = '未入帮'
            row.append(val)
        writer.writerow(row)
    output.seek(0)
    return send_file(io.BytesIO(output.getvalue().encode('utf-8-sig')),
                     mimetype='text/csv',
                     as_attachment=True,
                     download_name='attendance_export.csv')

@app.route('/upload_attendance', methods=['POST'])
@login_required
def upload_attendance():
    files = request.files.getlist('file')
    if not files or len(files) == 0:
        return render_template('attendance_upload.html', logged_in=session.get('logged_in', False), error='请上传csv或excel文件')
    import logging
    logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s', filename='fuzzy_match_debug.log', filemode='a')
    all_records = []
    member_dict = get_latest_member_dict()
    filtered_count = 0
    from datetime import datetime
    def is_valid_date(date_str):
        try:
            datetime.strptime(date_str, '%m-%d-%Y')
            return True
        except Exception:
            return False

    def parse_join_date(d_str):
        if not d_str: return None
        for fmt in ('%Y-%m-%d', '%m-%d-%Y', '%Y/%m/%d', '%m/%d/%Y'):
            try:
                return datetime.strptime(d_str.strip(), fmt)
            except (ValueError, TypeError):
                continue
        return None

    for file in files:
        filename = file.filename
        if not (filename.endswith('.csv') or filename.endswith('.xlsx') or filename.endswith('.xls')):
            continue
        
        if filename.endswith('.csv'):
            file.seek(0)
            try:
                lines = file.read().decode('utf-8-sig').splitlines()
            except UnicodeDecodeError:
                file.seek(0)
                lines = file.read().decode('gbk', errors='ignore').splitlines()
            
            csv_reader = csv.DictReader(lines)
            line_num = 1
            for row in csv_reader:
                line_num += 1
                remark = row.get('备注', '').strip()
                type_ = row.get('类型', '').strip()
                name = row.get('玩家名称', '').strip()
                job = row.get('职业', '').strip()
                date = row.get('日期', '').strip()

                if not date or not type_ or not is_valid_date(date):
                    logging.info(f"[DataFilter] 跳过无效数据: name={name}, job={job}, date={date}, type={type_}")
                    filtered_count += 1
                    continue
                
                if type_ == '帮众名单':
                    if not parse_join_date(remark):
                        error_msg = f"上传失败：文件 '{filename}' 第 {line_num} 行的玩家 '{name}' 缺少有效格式的入帮日期（备注列）。请使用 YYYY-MM-DD 或 MM-DD-YYYY 格式。"
                        return render_template('attendance_upload.html', logged_in=session.get('logged_in', False), error=error_msg)
                    status = '参与'
                else:
                    if '请假' in remark: status = '请假'
                    elif '缺勤' in remark: status = '缺勤'
                    elif '替补' in remark: status = '替补'
                    else: status = '参与'

                if type_ == '帮众名单':
                    all_records.append([name, job, date, type_, status, remark])
                else:
                    if not member_dict:
                        logging.info(f"[FuzzyMatch] 无帮众名单，跳过：{name}")
                        continue
                    match = get_close_matches(name, member_dict.keys(), n=1, cutoff=0.6)
                    if not match:
                        logging.info(f"[FuzzyMatch] 未匹配到：{name}，帮众名单keys={list(member_dict.keys())}")
                        continue
                    matched_name = match[0]
                    matched_job = member_dict.get(matched_name, job)
                    logging.info(f"[FuzzyMatch] {name} -> {matched_name} ({matched_job})")
                    all_records.append([matched_name, matched_job, date, type_, status, remark])
        else:
            df = pd.read_excel(file)
            for index, row in df.iterrows():
                line_num = index + 2
                remark = str(row.get('备注', '')).strip()
                type_ = str(row.get('类型', '')).strip()
                name = str(row.get('玩家名称', '')).strip()
                job = str(row.get('职业', '')).strip()
                date = str(row.get('日期', '')).strip()

                if not date or not type_:
                    logging.info(f"[DataFilter] 跳过无效数据: name={name}, job={job}, date={date}, type={type_}")
                    filtered_count += 1
                    continue

                if type_ == '帮众名单':
                    if not parse_join_date(remark):
                        error_msg = f"上传失败：文件 '{filename}' 第 {line_num} 行的玩家 '{name}' 缺少有效格式的入帮日期（备注列）。请使用 YYYY-MM-DD 或 MM-DD-YYYY 格式。"
                        return render_template('attendance_upload.html', logged_in=session.get('logged_in', False), error=error_msg)
                    status = '参与'
                else:
                    if '请假' in remark: status = '请假'
                    elif '缺勤' in remark: status = '缺勤'
                    elif '替补' in remark: status = '替补'
                    else: status = '参与'

                if type_ == '帮众名单':
                    all_records.append([name, job, date, type_, status, remark])
                else:
                    if not member_dict:
                        logging.info(f"[FuzzyMatch] 无帮众名单，跳过：{name}")
                        continue
                    match = get_close_matches(name, member_dict.keys(), n=1, cutoff=0.8)
                    if not match:
                        logging.info(f"[FuzzyMatch] 未匹配到：{name}，帮众名单keys={list(member_dict.keys())}")
                        continue
                    matched_name = match[0]
                    matched_job = member_dict.get(matched_name, job)
                    logging.info(f"[FuzzyMatch] {name} -> {matched_name} ({matched_job})")
                    all_records.append([matched_name, matched_job, date, type_, status, remark])

    if not all_records:
        msg = '未检测到有效数据或无有效帮众名单/匹配'
        if filtered_count > 0:
            msg += f'，已过滤{filtered_count}条无效数据（缺少日期或类型）'
        return render_template('attendance_upload.html', logged_in=session.get('logged_in', False), error=msg)
    
    insert_attendance(all_records)
    
    if filtered_count > 0:
        return render_template('attendance_upload.html', logged_in=session.get('logged_in', False), success=f'上传成功，已过滤{filtered_count}条无效数据（缺少日期或类型）')
    
    return redirect(url_for('attendance_table'))


# 新增：手动创建考勤记录页面
@app.route('/create_attendance', methods=['GET', 'POST'])
@login_required
def create_attendance():
    from attendance_db import get_latest_bangzhong_list, insert_attendance
    import datetime
    error = None
    success = None
    players = get_latest_bangzhong_list()
    if request.method == 'POST':
        date = request.form.get('date')
        type_ = request.form.get('type')
        status_dict = {}
        for name, _ in players:
            status_dict[name] = request.form.get(f'status_{name}')
        if not date or not type_:
            error = '日期和类型不能为空'
        else:
            # 日期格式转换 y-m-d -> m-d-Y
            try:
                from datetime import datetime
                date_obj = datetime.strptime(date, '%Y-%m-%d')
                date_str = date_obj.strftime('%m-%d-%Y')
            except Exception:
                date_str = date
            # 组装记录
            records = []
            for name, job in players:
                status = status_dict.get(name, '参与')
                records.append([name, job, date_str, type_, status, ''])
            insert_attendance(records)
            success = f'已成功创建{len(records)}条考勤记录'
    return render_template('create_attendance.html', players=players, error=error, success=success)


@app.route('/attendance_table')
def attendance_table():
    # 获取筛选参数
    selected_date = request.args.get('date')
    selected_type = request.args.get('type')
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    data = get_all_attendance()
    join_dates = get_join_dates()  # 获取入帮日期
    from attendance_db import get_latest_bangzhong_list
    players = get_latest_bangzhong_list()  # [(player_name, job), ...]
    date_types = set()
    from datetime import datetime
    def parse_db_date(d):
        try:
            return datetime.strptime(d, '%m-%d-%Y')
        except Exception:
            return None
    def parse_input_date(d):
        try:
            return datetime.strptime(d, '%Y-%m-%d')
        except Exception:
            return None
    start_dt = parse_input_date(start_date) if start_date else None
    end_dt = parse_input_date(end_date) if end_date else None
    # 只统计 type != '帮众名单' 的列
    filtered_records = []
    for player_name, job, date, type_, status, remark in data:
        if selected_date and date != selected_date:
            continue
        if selected_type and type_ != selected_type:
            continue
        if type_ == '帮众名单':
            continue
        date_dt = parse_db_date(date)
        if start_dt and (not date_dt or date_dt < start_dt):
            continue
        if end_dt and (not date_dt or date_dt > end_dt):
            continue
        filtered_records.append((player_name, date, type_, status))
        date_types.add((date, type_))
    # 优化列顺序：先按日期升序，同一天内接龙优先，其余按类型
    def type_priority(t):
        if t == '接龙':
            return 0
        elif t.startswith('帮战'):
            return 1
        else:
            return 2
    def parse_db_date(d):
        from datetime import datetime
        try:
            return datetime.strptime(d, '%m-%d-%Y')
        except Exception:
            return None
    date_types = sorted(list(date_types), key=lambda x: (parse_db_date(x[0]), type_priority(x[1]), x[1]))
    # 构造表格：玩家为行，日期/类型为列，单元格为状态（只用成员名称做主键，聚合所有职业）
    table = {}
    for player_name, date, type_, status in filtered_records:
        table.setdefault(player_name, {})[(date, type_)] = status

    # 为模板准备带 “未入帮” 状态的表格数据
    display_table = {}
    for p_name, p_job in players:
        display_table[p_name] = {'job': p_job, 'attendance': {}}
        join_date = join_dates.get(p_name)
        for date, type_ in date_types:
            status = table.get(p_name, {}).get((date, type_), '缺勤')
            event_date = parse_db_date(date)
            if status == '缺勤' and join_date and event_date and event_date < join_date:
                status = '未入帮'
            display_table[p_name]['attendance'][(date, type_)] = status

    players = list(players)
    # 传递筛选参数到模板
    return render_template('attendance_table.html', table=display_table, players=players, date_types=date_types, logged_in=session.get('logged_in', False), selected_date=selected_date, selected_type=selected_type, start_date=start_date, end_date=end_date)

@app.route('/update_attendance', methods=['POST'])
@login_required
def update_attendance_api():
    data = request.json
    player_name = data['player_name']
    date = data['date']
    type_ = data['type']
    status = data['status']
    update_attendance(player_name, date, type_, status)
    return jsonify({'success': True})

@app.route('/delete_attendance_column', methods=['POST'])
@login_required
def delete_attendance_column():
    date = request.form.get('date')
    type_ = request.form.get('type')
    if not date or not type_:
        return '缺少日期或类型参数', 400
    delete_attendance_by_date_and_type(date, type_)
    return redirect(url_for('attendance_table'))


@app.route('/delete_all_bangzhong', methods=['POST'])
@login_required
def delete_all_bangzhong():
    from attendance_db import delete_all_bangzhong
    delete_all_bangzhong()
    return redirect(url_for('attendance_table'))


# ====== AI Server配置 ======
AI_SERVER_URL = os.environ.get('AI_SERVER_URL', 'http://your_ai_server_url_here')
AI_API_KEY = os.environ.get('JIELONG_API_KEY', 'your_api_key_here')

# ====== Jielong Task DB 初始化 ======
from jielong_db import init_jielong_db, insert_jielong_task, update_jielong_debug_log, get_all_jielong_tasks, get_jielong_task
init_jielong_db()

@app.route('/jielong_admin')
@login_required
def jielong_admin():
    return render_template('jielong_admin.html', logged_in=True)


@app.route('/jielong/upload', methods=['POST'])
@login_required
def jielong_upload():
    # 支持 JSON 格式 raw_text 字段
    if request.is_json:
        raw_text = request.json.get('raw_text')
    else:
        raw_text = request.form.get('raw_text')
    if not raw_text:
        return jsonify({'error': '缺少原始文本'}), 400
    headers = {"X-API-KEY": AI_API_KEY, "Content-Type": "application/json"}
    resp = requests.post(
        f"{AI_SERVER_URL}/api/jielong/submit",
        json={"raw_text": raw_text},
        headers=headers
    )
    # 解析session_id并写入本地数据库
    try:
        data = resp.json()
        session_id = data.get('task_id') or data.get('session_id')
        if session_id:
            insert_jielong_task(session_id, raw_text)
    except Exception:
        pass
    return resp.text, resp.status_code



@app.route('/jielong/status')
@login_required
def jielong_status():
    task_id = request.args.get('task_id')
    if not task_id:
        return jsonify({'error': '缺少task_id参数'}), 400
    headers = {"X-API-KEY": AI_API_KEY}
    resp = requests.get(
        f"{AI_SERVER_URL}/api/jielong/status",
        params={"task_id": task_id},
        headers=headers
    )
    try:
        data = resp.json()
    except Exception:
        return resp.text, resp.status_code
    # 兼容AI server返回的report/debug_log/output.txt
    debug_log = data.get('debug_log')
    report = data.get('report')
    report_json = None
    output_txt = None
    # flask_jielong_server.py返回的report是dict，debug_log是output.txt内容
    if isinstance(report, dict):
        import json as _json
        report_json = _json.dumps(report, ensure_ascii=False, indent=2)
    if debug_log:
        output_txt = debug_log
    # 保存到本地
    if task_id and (debug_log or report_json or output_txt):
        update_jielong_debug_log(task_id, debug_log=debug_log, report_json=report_json, output_txt=output_txt)
    result = {
        'status': data.get('status'),
        'report': report,
        'debug_log': debug_log,
        'error': data.get('error')
    }
    return jsonify(result)
# 查询所有jielong任务和下载debug log
@app.route('/jielong/history')
@login_required
def jielong_history():
    tasks = get_all_jielong_tasks()
    return render_template('jielong_history.html', tasks=tasks, logged_in=True)


# 下载 debug_log
@app.route('/jielong/download_debug/<session_id>')
@login_required
def download_jielong_debug(session_id):
    task = get_jielong_task(session_id)
    if not task or not task[3]:
        return 'No debug log found', 404
    from flask import Response
    return Response(task[3], mimetype='text/plain', headers={"Content-Disposition":f"attachment;filename=debug_log_{session_id}.txt"})

# 下载 report_json
@app.route('/jielong/download_report/<session_id>')
@login_required
def download_jielong_report(session_id):
    task = get_jielong_task(session_id)
    if not task or not task[4]:
        return 'No report found', 404
    from flask import Response
    return Response(task[4], mimetype='application/json', headers={"Content-Disposition":f"attachment;filename=report_{session_id}.json"})

# 下载 output_txt
@app.route('/jielong/download_output/<session_id>')
@login_required
def download_jielong_output(session_id):
    task = get_jielong_task(session_id)
    if not task or not task[5]:
        return 'No output.txt found', 404
    from flask import Response
    return Response(task[5], mimetype='text/plain', headers={"Content-Disposition":f"attachment;filename=output_{session_id}.txt"})


# /jielong/log/<task_id>接口已移除，debug日志内容直接在status接口返回


# 静态文件下载（示例CSV）
from flask import send_from_directory
@app.route('/static/attendance_sample.csv')
def download_attendance_sample():
    static_dir = os.path.join(os.path.dirname(__file__), 'Static')
    return send_from_directory(static_dir, 'attendance_sample.csv', as_attachment=True)

if __name__ == '__main__':
    app.run(debug=False, host='0.0.0.0', port=8080)
